﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlServerCe;
using System.Configuration; 

namespace Calc
{
    class ManageItems
    {
        public string GetConnectionString()
        {

            string cn = "Data Source=C:\\Calc1232\\Calc123DB.sdf";
            //"server=WINDOWS-13422A2\\SQLEXPRESS;user id=sa;password=kalyan;database=123calc;Trusted_Connection=Yes";
            return cn;
        }
        public DataSet GetGroups(Entities objEntities)
        {
            //SqlCeParameter parms1;
            DataSet ds = new DataSet();
            SqlCeConnection cn = new SqlCeConnection(GetConnectionString());
            SqlCeCommand cmd = new SqlCeCommand();

            if (objEntities.Action == 0)
                cmd.CommandText = "Select GroupID,GroupName From Groups order by GroupName";
            else if (objEntities.Action == 1)
                cmd.CommandText = "Insert into Groups (GroupName,CreatedDate) values ('" + objEntities.GroupName + "', '" + DateTime.Now.ToString() + "')";
            else if (objEntities.Action == 2)
                cmd.CommandText = "Update Groups set GroupName='"+objEntities.GroupName+"' where GroupID="+objEntities.GroupID;
            
            cmd.CommandType = CommandType.Text;
            cmd.Connection = cn;
            //parms1 = new SqlParameter("@GroupID", SqlDbType.Int);
            //parms1.Value = objEntities.GroupID;
            //cmd.Parameters.Add(parms1);

            //parms1 = new SqlParameter("@GroupName", SqlDbType.VarChar, 50);
            //parms1.Value = objEntities.GroupName;
            //cmd.Parameters.Add(parms1);

            //parms1 = new SqlParameter("@Action", SqlDbType.Int);
            //parms1.Value = objEntities.Action;
            //cmd.Parameters.Add(parms1);

            SqlCeDataAdapter da = new SqlCeDataAdapter(cmd);
            da.Fill(ds, "Groups");

            return ds;

        }
        public DataSet GetItems(Entities objEntities)
        {
            DataSet ds = new DataSet();
            SqlParameter parms;
            SqlCeConnection cn = new SqlCeConnection(GetConnectionString());
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = cn;
            cmd.CommandType = CommandType.Text;

            if (objEntities.Action == 0)
                cmd.CommandText = "select Items.ItemID,Items.ItemName,Groups.GroupName,Items.GroupID,Items.MaterialTypeID,MaterialType.MaterialName from Items"
                    + " inner join Groups on Items.GroupID=Groups.GroupID"
                    + " inner join MaterialType on Items.MaterialTypeID=MaterialType.MaterialTypeID";
            else if (objEntities.Action == 1)
                cmd.CommandText = "select Items.ItemID,Items.ItemName,Groups.GroupName,Items.GroupID,MaterialType.MaterialName,Items.MaterialTypeID,"
                    + " Items.Cost,Items.CostPer,Items.Waste,Items.Coverage,Items.Labour,Items.LabourType from Items"
                    + " inner join Groups on Items.GroupID=Groups.GroupID"
                    + " inner join MaterialType on Items.MaterialTypeID=MaterialType.MaterialTypeID"
                    + " where Items.ItemID=" + objEntities.ItemID;
            else if (objEntities.Action == 2)
                cmd.CommandText = "select Items.ItemID,Items.ItemName,Groups.GroupName,Items.GroupID,MaterialType.MaterialName from Items"
                    +" inner join Groups on Items.GroupID=Groups.GroupID"
                    +" inner join MaterialType on Items.MaterialTypeID=MaterialType.MaterialTypeID"
                    + " where Items.GroupID=" + objEntities.GroupID;
 

            //parms = new SqlParameter("@ItemID", SqlDbType.Int);
            //parms.Value = objEntities.ItemID;
            //cmd.Parameters.Add(parms);

            //parms = new SqlParameter("@GroupID", SqlDbType.Int);
            //parms.Value = objEntities.GroupID;
            //cmd.Parameters.Add(parms);

            //parms = new SqlParameter("@Action", SqlDbType.Int);
            //parms.Value = objEntities.Action;
            //cmd.Parameters.Add(parms);

            SqlCeDataAdapter da = new SqlCeDataAdapter(cmd);
            da.Fill(ds, "Items");

            return ds;
           
        }
        public DataSet GetAssemblies(Entities objEntities)
        {
            DataSet ds = new DataSet();
            SqlCeConnection cn = new SqlCeConnection(GetConnectionString());
            SqlCeCommand cmd = new SqlCeCommand();

            if (objEntities.Action == 0)
                cmd.CommandText = "Select AssemblyID,AssemblyName From Assemblies order by AssemblyName";
            else if (objEntities.Action == 1)
                cmd.CommandText = "Insert into Assemblies (AssemblyName,CreatedDate) values ('" + objEntities.AssemblyName + "', '" + DateTime.Now.ToString() + "')";
            else if (objEntities.Action == 2)
                cmd.CommandText = "Update Assemblies set AssemblyName='" + objEntities.AssemblyName + "' where AssemblyID=" + objEntities.AssemblyID;

            cmd.CommandType = CommandType.Text;
            cmd.Connection = cn;

            SqlCeDataAdapter da = new SqlCeDataAdapter(cmd);
            da.Fill(ds, "Assemblies");

            return ds;
        }

        public DataSet GetItemsByAssemblies(Entities objEntities)
        {
            DataSet ds = new DataSet();
            SqlCeConnection cn = new SqlCeConnection(GetConnectionString());
            SqlCeCommand cmd = new SqlCeCommand();

            cmd.CommandText = "Select ItemID,ItemName from Items where AssemblyID=" + objEntities.AssemblyID;
            cmd.Connection = cn;

            SqlCeDataAdapter da = new SqlCeDataAdapter(cmd);
            da.Fill(ds, "ItemsAss");

            return ds;
        }
        public DataSet GetMaterialType(Entities objEntities)
        {
            DataSet ds = new DataSet();
            //SqlParameter parms;
            SqlCeConnection cn = new SqlCeConnection(GetConnectionString());
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = cn;
            if (objEntities.Action == 0)
                cmd.CommandText = "Select MaterialTypeID,MaterialName from MaterialType order by MaterialName";
            cmd.CommandType = CommandType.Text;

            //parms = new SqlParameter("@Action", SqlDbType.Int);
            //parms.Value = objEntities.Action;
            //cmd.Parameters.Add(parms);

            SqlCeDataAdapter da = new SqlCeDataAdapter(cmd);
            da.Fill(ds, "MaterialType");

            return ds;
        }

        public void InsertItems(Entities objEntities)
        {
            SqlParameter parms;
            SqlCeConnection cn = new SqlCeConnection(GetConnectionString());
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = cn;
            cmd.CommandType = CommandType.Text;

            if (objEntities.Action == 0)
                cmd.CommandText = "insert into Items (ItemName,MaterialTypeID,GroupID,Cost,CostPer,Coverage,Waste,Labour,LabourType) values"
                    + " ('" + objEntities.ItemName + "'," + objEntities.MaterialTypeID + "," + objEntities.GroupID + "," + objEntities.Cost
                    + ",'" + objEntities.CostPer + "','" + objEntities.Coverage + "','" + objEntities.Waste + "','" + objEntities.Labour
                    + "'," + objEntities.LabourType + ")";
            else
                cmd.CommandText = "update Items set ItemName='"+objEntities.ItemName+"',MaterialTypeID="+objEntities.MaterialTypeID
                    + ",GroupID=" + objEntities.GroupID + ",Cost=" + objEntities.Cost + ",CostPer='"+objEntities.CostPer
                    + "',Coverage='" + objEntities.Coverage + "'," + "Waste='" + objEntities.Waste + "',Labour='"+objEntities.Labour
                    +"',LabourType='"+objEntities.LabourType+"' where ItemID="+objEntities.ItemID;

            //parms = new SqlParameter("@ItemID", SqlDbType.Int);
            //parms.Value = objEntities.ItemID;
            //cmd.Parameters.Add(parms);

            //parms = new SqlParameter("@ItemName", SqlDbType.VarChar, 50);
            //parms.Value = objEntities.ItemName;
            //cmd.Parameters.Add(parms);

            //parms = new SqlParameter("@MaterialTypeID", SqlDbType.Int);
            //parms.Value = objEntities.MaterialTypeID;
            //cmd.Parameters.Add(parms);

            //parms = new SqlParameter("@GroupID", SqlDbType.Int);
            //parms.Value = objEntities.GroupID;
            //cmd.Parameters.Add(parms);

            //parms = new SqlParameter("@Cost", SqlDbType.Money);
            //parms.Value = objEntities.Cost;
            //cmd.Parameters.Add(parms);

            //parms = new SqlParameter("@CostPer", SqlDbType.VarChar, 50);
            //parms.Value = objEntities.CostPer;
            //cmd.Parameters.Add(parms);

            //parms = new SqlParameter("@Coverage", SqlDbType.VarChar, 50);
            //parms.Value = objEntities.Coverage;
            //cmd.Parameters.Add(parms);

            //parms = new SqlParameter("Waste", SqlDbType.VarChar, 50);
            //parms.Value = objEntities.Waste;
            //cmd.Parameters.Add(parms);

            //parms = new SqlParameter("@Labour", SqlDbType.VarChar, 50);
            //parms.Value = objEntities.Labour;
            //cmd.Parameters.Add(parms);

            //parms = new SqlParameter("@LabourType", SqlDbType.Int);
            //parms.Value = objEntities.LabourType;
            //cmd.Parameters.Add(parms);

            //parms = new SqlParameter("@Action", SqlDbType.Int);
            //parms.Value = objEntities.Action;
            //cmd.Parameters.Add(parms);

            cn.Open();
            cmd.ExecuteNonQuery();
            cn.Close();
        }
        public DataSet GetMaxItemID()
        {
            DataSet ds = new DataSet();
            SqlCeConnection cn = new SqlCeConnection(GetConnectionString());
            SqlCeCommand cmd = new SqlCeCommand("Select max(ItemID) from Items ", cn);
            SqlCeDataAdapter da = new SqlCeDataAdapter(cmd);
            da.Fill(ds,"ItemID");
            return ds;
        }

    }
    
}
